campaign

Table: campaign

The campaign table stores information about campaigns, including their timelines, eligibility criteria, and metadata for auditing.
It helps manage campaign lifecycles and track which users created or modified them.

---

Columns

Column Name	Data Type	Constraints	Description
campaignId	int(11)	NOT NULL, AUTO_INCREMENT	Unique identifier for each campaign
name	varchar(50)	UNIQUE	Campaign name (must be unique)
startDate	datetime	NULL	Campaign start date and time
endDate	datetime	NULL	Campaign end date and time
locationId	int(11)	NULL	Identifier for campaign location
description	varchar(300)	NULL	Detailed description of the campaign
createdByUserId	int(11)	NULL	User who created the campaign
createdDate	datetime	NULL	Timestamp when campaign was created
lastEditedByUserId	int(11)	NULL	User who last modified the campaign
lastEditedDate	datetime	NULL	Timestamp of last modification
voided	tinyint(1)	DEFAULT 0	Soft delete flag (0 = active, 1 = voided)
voidedDate	datetime	NULL	Timestamp when campaign was voided
ageLimitLower	double	NULL	Minimum eligible age for campaign
ageLimitUpper	double	NULL	Maximum eligible age for campaign

---

Indexes

1. PRIMARY - Primary key on campaignId
2. name - Unique index on name
3. campaign_createdByUserId_user_mappedId_FK - Index on createdByUserId
4. campaign_lastEditedByUserId_user_mappedId_FK - Index on lastEditedByUserId

---

Foreign Key Relations

1. campaign_ibfk_1

   • Links createdByUserId to user.mappedId
   • Tracks which user created the campaign

2. campaign_ibfk_2

   • Links lastEditedByUserId to user.mappedId
   • Tracks which user last edited the campaign

---

Usage Notes

• Soft deletion is managed via the voided flag to preserve historical data.
• Campaign names must be unique across the system.
• Age limits (ageLimitLower and ageLimitUpper) define eligibility criteria.
• Audit trail is maintained via createdByUserId, lastEditedByUserId, createdDate, and lastEditedDate.
• Location references (locationId) link campaigns to specific geographic areas.